import urllib.request
import pymysql
import time
from lxml import etree
import random
db=pymysql.connect(host="localhost",port=3307,user="root",password="1234", db="fruitdb",charset='utf8')
page=1
number=1
while page <=20:
    time.sleep(2)
    req='https://www.qidian.com/rank/recom?style=1&page='+str(page)
    response=urllib.request.urlopen(req)
    data=response.read().decode('utf-8')
    # print(data)
    selector = etree.HTML(data)
    # print(selector)
    list=selector.xpath('//div[@class="book-img-text"]/ul/li')
    # print(list)
    cursor=db.cursor()
    for item in list:
        title=item.xpath('./div[2]/h4/a/text()')[0]
        img=item.xpath('./div[1]/a/img/@src')[0]
        authorname=item.xpath('./div[2]/p[1]/a[1]/text()')[0]
        # kind=random.choice(['玄幻','奇幻','武侠','仙侠','都市','现实','军事','历史','游戏','体育','科幻','灵异'])
        kind=item.xpath('./div[2]/p[1]/a[2]/text()')[0]
        state=item.xpath('./div[2]/p[1]/span/text()')[0]
        intro=item.xpath('./div[2]/p[2]/text()')[0].strip()
        # clickcount=item.xpath('./div[1]/div[2]/div[1]/span[4]')[0].split(':')

        # print(sellcount,lencount)
        sql="insert into ranking(title,img,authorname,kind,state,intro,ticketcount,clickcount,sellcount,lencount)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(title,img,authorname,kind,state,intro,ticketcount,clickcount,sellcount,lencount)
        # sql="select title,img,authorname,kind,state,intro,ticketcount from ranking order by ticketcount desc"
        cursor.execute(sql)
        # result=cursor.fetchall()
    db.commit()
    page += 1